{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Streetcar Delay Prediction - Data Preparation Geocode Specific\n",
    "\n",
    "Use dataset covering Toronto Transit Commission (TTC) streetcar delays 2014 - present to predict future delays and come up with recommendations for avoiding delays.\n",
    "\n",
    "Source dataset: : https://www.toronto.ca/city-government/data-research-maps/open-data/open-data-catalogue/#e8f359f0-2f47-3058-bf64-6ec488de52da\n",
    "\n",
    "This notebook contains the data preparation steps specific to mapping free-form location descriptions to latitude and longitude\n",
    "\n",
    "- use the Google Maps API Web Services for Python  https://github.com/googlemaps/google-maps-services-python\n",
    "- generate the latitude and longitude values for locations and create new columns in the output dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Streetcar routes\n",
    "\n",
    "From https://www.ttc.ca/Routes/Streetcars.jsp\n",
    "\n",
    "<table style=\"border: none\" align=\"left\">\n",
    "   </tr>\n",
    "   <tr style=\"border: none\">\n",
    "       <th style=\"border: none\"><img src=\"https://raw.githubusercontent.com/ryanmark1867/streetcarnov3/master/streetcar%20routes.jpg\" width=\"600\" alt=\"Icon\"> </th>\n",
    "   </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get path and load dataframe saved from previous data preparation step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "import matplotlib.pyplot as plt\n",
    "# import seaborn as sns\n",
    "import datetime\n",
    "import os\n",
    "\n",
    "remove_bad_values = False\n",
    "city_name = 'Toronto'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "raw path is C:\\personal\\manning\\deep_learning_for_structured_data\\notebooks\n"
     ]
    }
   ],
   "source": [
    "# get the directory for that this notebook is in\n",
    "rawpath = os.getcwd()\n",
    "print(\"raw path is\",rawpath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "path is C:\\personal\\manning\\deep_learning_for_structured_data\\data\n"
     ]
    }
   ],
   "source": [
    "# data is in a directory called \"data\" that is a sibling to the directory containing the notebook\n",
    "path = os.path.abspath(os.path.join(rawpath, '..', 'data'))\n",
    "print(\"path is\", path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# constants for the streetcar problem\n",
    "# same values saved in data_preparation notebook: pickled_input_dataframe, pickled_output_dataframe\n",
    "pickled_data_file = '2014_2018.pkl'\n",
    "#pickled_dataframe = '2014_2018_df.pkl'\n",
    "pickled_dataframe = '2014_2018_df_cleaned_keep_bad_apr23.pkl'\n",
    "pickled_output_dataframe = '2014_2018_df_cleaned_keep_bad_loc_geocoded.pkl'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Report Date</th>\n",
       "      <th>Route</th>\n",
       "      <th>Time</th>\n",
       "      <th>Day</th>\n",
       "      <th>Location</th>\n",
       "      <th>Incident</th>\n",
       "      <th>Min Delay</th>\n",
       "      <th>Min Gap</th>\n",
       "      <th>Direction</th>\n",
       "      <th>Vehicle</th>\n",
       "      <th>Report Date Time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Report Date Time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>General Delay</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4028</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-01-01 02:14:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>511</td>\n",
       "      <td>02:14:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4018</td>\n",
       "      <td>2016-01-01 02:14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-01-01 02:22:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>02:22:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4201</td>\n",
       "      <td>2016-01-01 02:22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-01-01 03:28:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>03:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4251</td>\n",
       "      <td>2016-01-01 03:28:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-01-01 14:28:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>14:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4242</td>\n",
       "      <td>2016-01-01 14:28:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Report Date Route      Time     Day  \\\n",
       "Report Date Time                                          \n",
       "2016-01-01 00:00:00  2016-01-01   505  00:00:00  Friday   \n",
       "2016-01-01 02:14:00  2016-01-01   511  02:14:00  Friday   \n",
       "2016-01-01 02:22:00  2016-01-01   301  02:22:00  Friday   \n",
       "2016-01-01 03:28:00  2016-01-01   301  03:28:00  Friday   \n",
       "2016-01-01 14:28:00  2016-01-01   501  14:28:00  Friday   \n",
       "\n",
       "                                                      Location       Incident  \\\n",
       "Report Date Time                                                                \n",
       "2016-01-01 00:00:00  dundas west stationt to broadview station  General Delay   \n",
       "2016-01-01 02:14:00                     fleet st. and strachan     Mechanical   \n",
       "2016-01-01 02:22:00            queen st. west and roncesvalles     Mechanical   \n",
       "2016-01-01 03:28:00          lake shore blvd. and superior st.     Mechanical   \n",
       "2016-01-01 14:28:00               roncesvalles to neville park     Mechanical   \n",
       "\n",
       "                     Min Delay  Min Gap Direction Vehicle    Report Date Time  \n",
       "Report Date Time                                                               \n",
       "2016-01-01 00:00:00        7.0     14.0         w    4028 2016-01-01 00:00:00  \n",
       "2016-01-01 02:14:00       10.0     20.0         e    4018 2016-01-01 02:14:00  \n",
       "2016-01-01 02:22:00        9.0     18.0         w    4201 2016-01-01 02:22:00  \n",
       "2016-01-01 03:28:00       20.0     40.0         e    4251 2016-01-01 03:28:00  \n",
       "2016-01-01 14:28:00        6.0     12.0         e    4242 2016-01-01 14:28:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_name = os.path.join(path,pickled_dataframe)\n",
    "df = pd.read_pickle(file_name)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(69603, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loc_unique dundas west stationt to broadview station\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>fleet st. and strachan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Location\n",
       "0  dundas west stationt to broadview station\n",
       "1                     fleet st. and strachan\n",
       "2            queen st. west and roncesvalles\n",
       "3          lake shore blvd. and superior st.\n",
       "4               roncesvalles to neville park"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe just containing\n",
    "# gapminder['continent'].unique().tolist\n",
    "loc_unique = df['Location'].unique().tolist()\n",
    "print(\"loc_unique\", loc_unique[0])\n",
    "# pd.DataFrame(q_list, columns=['q_data'])\n",
    "df_unique = pd.DataFrame(loc_unique, columns=['Location'])\n",
    "df_unique.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10074, 1)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unique.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set up geocode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting googlemaps\n",
      "  Downloading https://files.pythonhosted.org/packages/07/b8/bd7ab78014a4290853250ac8a1744c5a200e569811b7e0cc9222d38fc296/googlemaps-4.2.0-py3-none-any.whl\n",
      "Requirement already satisfied, skipping upgrade: requests<3.0,>=2.20.0 in c:\\users\\ryanm\\appdata\\local\\programs\\python\\python37\\lib\\site-packages (from googlemaps) (2.22.0)\n",
      "Requirement already satisfied, skipping upgrade: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in c:\\users\\ryanm\\appdata\\local\\programs\\python\\python37\\lib\\site-packages (from requests<3.0,>=2.20.0->googlemaps) (1.25.3)\n",
      "Requirement already satisfied, skipping upgrade: idna<2.9,>=2.5 in c:\\users\\ryanm\\appdata\\local\\programs\\python\\python37\\lib\\site-packages (from requests<3.0,>=2.20.0->googlemaps) (2.8)\n",
      "Requirement already satisfied, skipping upgrade: chardet<3.1.0,>=3.0.2 in c:\\users\\ryanm\\appdata\\local\\programs\\python\\python37\\lib\\site-packages (from requests<3.0,>=2.20.0->googlemaps) (3.0.4)\n",
      "Requirement already satisfied, skipping upgrade: certifi>=2017.4.17 in c:\\users\\ryanm\\appdata\\local\\programs\\python\\python37\\lib\\site-packages (from requests<3.0,>=2.20.0->googlemaps) (2019.6.16)\n",
      "Installing collected packages: googlemaps\n",
      "Successfully installed googlemaps-4.2.0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "You are using pip version 19.0.3, however version 20.0.2 is available.\n",
      "You should consider upgrading via the 'python -m pip install --upgrade pip' command.\n"
     ]
    }
   ],
   "source": [
    "! pip install -U googlemaps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "geocode result {'lat': 43.61496169999999, 'lng': -79.4886581}\n"
     ]
    }
   ],
   "source": [
    "import googlemaps\n",
    "\n",
    "# API key comes from https://developers.google.com/maps/documentation/embed/get-api-key\n",
    "# NOTE: to run this code you will need to generate your own API key and enter it as the key value in the line below\n",
    "gmaps = googlemaps.Client(key='')\n",
    "\n",
    "# Geocoding an address\n",
    "geocode_result = gmaps.geocode('lake shore blvd. and superior st., Toronto')\n",
    "\n",
    "print(\"geocode result\",geocode_result[0][\"geometry\"][\"location\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# given an address / junction, return a list containg the latitude and longitude values returned by geocode api\n",
    "\n",
    "def get_geocode_result(junction):\n",
    "    \n",
    "    geo_string = junction+\", \"+city_name\n",
    "    # print(\"geo_string is\", geo_string)\n",
    "    geocode_result = gmaps.geocode(geo_string)\n",
    "    # check to see if the result is empty and if so return zeros to indicate unparseable junction value\n",
    "    if len(geocode_result) > 0:\n",
    "        locs = geocode_result[0][\"geometry\"][\"location\"]\n",
    "        return [locs[\"lat\"], locs[\"lng\"]]\n",
    "    else:\n",
    "        return [0.0,0.0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "locs  [0.0, 0.0]\n"
     ]
    }
   ],
   "source": [
    "# test geocode api with value that will return empty result\n",
    "\n",
    "locs = get_geocode_result(\"roncesvalles to longbranch\")\n",
    "print(\"locs \",locs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43.6471969"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test geocode api with value that will return non-empty result\n",
    "get_geocode_result(\"queen and bathurst\")[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(69603, 11)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# to avoid making multiple calls to the geocode API, bring in the latitude and longitude values as a single \n",
    "# column to a dataframe containing just the unique location values and once we have that go through steps\n",
    "# to get the desired columns in the overall dataframe\n",
    "df_unique['lat_long'] = df_unique.Location.apply(lambda s: get_geocode_result(s))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>lat_long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>[43.6362976, -79.4096351]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>[43.61496169999999, -79.4886581]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Location                          lat_long\n",
       "0  dundas west stationt to broadview station                        [0.0, 0.0]\n",
       "1                     fleet st. and strachan         [43.6362976, -79.4096351]\n",
       "2            queen st. west and roncesvalles  [43.64533489999999, -79.4131843]\n",
       "3          lake shore blvd. and superior st.  [43.61496169999999, -79.4886581]\n",
       "4               roncesvalles to neville park                        [0.0, 0.0]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unique.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10074, 2)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unique.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>[43.6362976, -79.4096351]</td>\n",
       "      <td>43.636298</td>\n",
       "      <td>-79.409635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "      <td>43.645335</td>\n",
       "      <td>-79.413184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>[43.61496169999999, -79.4886581]</td>\n",
       "      <td>43.614962</td>\n",
       "      <td>-79.488658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Location  \\\n",
       "0  dundas west stationt to broadview station   \n",
       "1                     fleet st. and strachan   \n",
       "2            queen st. west and roncesvalles   \n",
       "3          lake shore blvd. and superior st.   \n",
       "4               roncesvalles to neville park   \n",
       "\n",
       "                           lat_long   latitude  longitude  \n",
       "0                        [0.0, 0.0]   0.000000   0.000000  \n",
       "1         [43.6362976, -79.4096351]  43.636298 -79.409635  \n",
       "2  [43.64533489999999, -79.4131843]  43.645335 -79.413184  \n",
       "3  [43.61496169999999, -79.4886581]  43.614962 -79.488658  \n",
       "4                        [0.0, 0.0]   0.000000   0.000000  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# derive latitude and longitude columns from list column\n",
    "# df[\"new_col\"] = df[\"A\"].str[0]\n",
    "df_unique[\"latitude\"] = df_unique[\"lat_long\"].str[0]\n",
    "df_unique[\"longitude\"] = df_unique[\"lat_long\"].str[1]\n",
    "df_unique.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10074, 4)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unique.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Report Date</th>\n",
       "      <th>Route</th>\n",
       "      <th>Time</th>\n",
       "      <th>Day</th>\n",
       "      <th>Location</th>\n",
       "      <th>Incident</th>\n",
       "      <th>Min Delay</th>\n",
       "      <th>Min Gap</th>\n",
       "      <th>Direction</th>\n",
       "      <th>Vehicle</th>\n",
       "      <th>Report Date Time</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>General Delay</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4028</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>511</td>\n",
       "      <td>02:14:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4018</td>\n",
       "      <td>2016-01-01 02:14:00</td>\n",
       "      <td>[43.6362976, -79.4096351]</td>\n",
       "      <td>43.636298</td>\n",
       "      <td>-79.409635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>02:22:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4201</td>\n",
       "      <td>2016-01-01 02:22:00</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "      <td>43.645335</td>\n",
       "      <td>-79.413184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>03:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4251</td>\n",
       "      <td>2016-01-01 03:28:00</td>\n",
       "      <td>[43.61496169999999, -79.4886581]</td>\n",
       "      <td>43.614962</td>\n",
       "      <td>-79.488658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>14:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4242</td>\n",
       "      <td>2016-01-01 14:28:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Report Date Route      Time     Day  \\\n",
       "0  2016-01-01   505  00:00:00  Friday   \n",
       "1  2016-01-01   511  02:14:00  Friday   \n",
       "2  2016-01-01   301  02:22:00  Friday   \n",
       "3  2016-01-01   301  03:28:00  Friday   \n",
       "4  2016-01-01   501  14:28:00  Friday   \n",
       "\n",
       "                                    Location       Incident  Min Delay  \\\n",
       "0  dundas west stationt to broadview station  General Delay        7.0   \n",
       "1                     fleet st. and strachan     Mechanical       10.0   \n",
       "2            queen st. west and roncesvalles     Mechanical        9.0   \n",
       "3          lake shore blvd. and superior st.     Mechanical       20.0   \n",
       "4               roncesvalles to neville park     Mechanical        6.0   \n",
       "\n",
       "   Min Gap Direction Vehicle    Report Date Time  \\\n",
       "0     14.0         w    4028 2016-01-01 00:00:00   \n",
       "1     20.0         e    4018 2016-01-01 02:14:00   \n",
       "2     18.0         w    4201 2016-01-01 02:22:00   \n",
       "3     40.0         e    4251 2016-01-01 03:28:00   \n",
       "4     12.0         e    4242 2016-01-01 14:28:00   \n",
       "\n",
       "                           lat_long   latitude  longitude  \n",
       "0                        [0.0, 0.0]   0.000000   0.000000  \n",
       "1         [43.6362976, -79.4096351]  43.636298 -79.409635  \n",
       "2  [43.64533489999999, -79.4131843]  43.645335 -79.413184  \n",
       "3  [43.61496169999999, -79.4886581]  43.614962 -79.488658  \n",
       "4                        [0.0, 0.0]   0.000000   0.000000  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join df_unique dataframe with original df dataframe on Location column to get latitude and longitude cols in original df dataframe\n",
    "# result1 = pd.merge(date_frame, routedirection_frame, on='count', how='outer')\n",
    "df_out = pd.merge(df, df_unique, on=\"Location\", how='left')\n",
    "df_out.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Report Date</th>\n",
       "      <th>Route</th>\n",
       "      <th>Time</th>\n",
       "      <th>Day</th>\n",
       "      <th>Location</th>\n",
       "      <th>Incident</th>\n",
       "      <th>Min Delay</th>\n",
       "      <th>Min Gap</th>\n",
       "      <th>Direction</th>\n",
       "      <th>Vehicle</th>\n",
       "      <th>Report Date Time</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>General Delay</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4028</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>511</td>\n",
       "      <td>02:14:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4018</td>\n",
       "      <td>2016-01-01 02:14:00</td>\n",
       "      <td>[43.6362976, -79.4096351]</td>\n",
       "      <td>43.636298</td>\n",
       "      <td>-79.409635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>02:22:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4201</td>\n",
       "      <td>2016-01-01 02:22:00</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "      <td>43.645335</td>\n",
       "      <td>-79.413184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>03:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4251</td>\n",
       "      <td>2016-01-01 03:28:00</td>\n",
       "      <td>[43.61496169999999, -79.4886581]</td>\n",
       "      <td>43.614962</td>\n",
       "      <td>-79.488658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>14:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4242</td>\n",
       "      <td>2016-01-01 14:28:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>15:42:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>broadview station loop</td>\n",
       "      <td>Investigation</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4187</td>\n",
       "      <td>2016-01-01 15:42:00</td>\n",
       "      <td>[43.677135, -79.35820799999999]</td>\n",
       "      <td>43.677135</td>\n",
       "      <td>-79.358208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>504</td>\n",
       "      <td>15:54:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>broadview and queen</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4181</td>\n",
       "      <td>2016-01-01 15:54:00</td>\n",
       "      <td>[43.6593626, -79.34769709999999]</td>\n",
       "      <td>43.659363</td>\n",
       "      <td>-79.347697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>16:05:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to humber loop</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4245</td>\n",
       "      <td>2016-01-01 16:05:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>506</td>\n",
       "      <td>16:27:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>main station</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4092</td>\n",
       "      <td>2016-01-01 16:27:00</td>\n",
       "      <td>[43.6890219, -79.3016857]</td>\n",
       "      <td>43.689022</td>\n",
       "      <td>-79.301686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>510</td>\n",
       "      <td>16:34:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>richmond st. and spadina</td>\n",
       "      <td>Diversion</td>\n",
       "      <td>41.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>s</td>\n",
       "      <td>bad vehicle</td>\n",
       "      <td>2016-01-01 16:34:00</td>\n",
       "      <td>[43.6478469, -79.39588049999999]</td>\n",
       "      <td>43.647847</td>\n",
       "      <td>-79.395880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>16:44:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to humber</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4220</td>\n",
       "      <td>2016-01-01 16:44:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>17:36:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>dundas st. west and huron st.</td>\n",
       "      <td>Held By</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4039</td>\n",
       "      <td>2016-01-01 17:36:00</td>\n",
       "      <td>[43.653332, -79.3961386]</td>\n",
       "      <td>43.653332</td>\n",
       "      <td>-79.396139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>506</td>\n",
       "      <td>19:15:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>bathurst and college</td>\n",
       "      <td>Utilized Off Route</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4095</td>\n",
       "      <td>2016-01-01 19:15:00</td>\n",
       "      <td>[43.6564647, -79.4077012]</td>\n",
       "      <td>43.656465</td>\n",
       "      <td>-79.407701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>509</td>\n",
       "      <td>21:25:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>cne loop</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4406</td>\n",
       "      <td>2016-01-01 21:25:00</td>\n",
       "      <td>[43.63441590000001, -79.4258807]</td>\n",
       "      <td>43.634416</td>\n",
       "      <td>-79.425881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>511</td>\n",
       "      <td>22:54:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>bathurst and wellington</td>\n",
       "      <td>Held By</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>b</td>\n",
       "      <td>4095</td>\n",
       "      <td>2016-01-01 22:54:00</td>\n",
       "      <td>[43.6426297, -79.4021618]</td>\n",
       "      <td>43.642630</td>\n",
       "      <td>-79.402162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>510</td>\n",
       "      <td>23:58:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>queen quay loop and spadina</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>n</td>\n",
       "      <td>4132</td>\n",
       "      <td>2016-01-01 23:58:00</td>\n",
       "      <td>[43.6384598, -79.3916564]</td>\n",
       "      <td>43.638460</td>\n",
       "      <td>-79.391656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>501</td>\n",
       "      <td>01:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>roncesvallesue and the queensway</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4223</td>\n",
       "      <td>2016-01-02 01:00:00</td>\n",
       "      <td>[43.63878890000001, -79.446111]</td>\n",
       "      <td>43.638789</td>\n",
       "      <td>-79.446111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>501</td>\n",
       "      <td>06:01:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>queen and roncevalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4247</td>\n",
       "      <td>2016-01-02 06:01:00</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "      <td>43.645335</td>\n",
       "      <td>-79.413184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>504</td>\n",
       "      <td>06:51:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>king and spadina</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>32.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4186</td>\n",
       "      <td>2016-01-02 06:51:00</td>\n",
       "      <td>[43.6454419, -79.3951458]</td>\n",
       "      <td>43.645442</td>\n",
       "      <td>-79.395146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>504</td>\n",
       "      <td>06:55:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>king and spadina</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>28.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4154</td>\n",
       "      <td>2016-01-02 06:55:00</td>\n",
       "      <td>[43.6454419, -79.3951458]</td>\n",
       "      <td>43.645442</td>\n",
       "      <td>-79.395146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>501</td>\n",
       "      <td>08:04:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>humber loop</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4208</td>\n",
       "      <td>2016-01-02 08:04:00</td>\n",
       "      <td>[43.63079099999999, -79.478622]</td>\n",
       "      <td>43.630791</td>\n",
       "      <td>-79.478622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>512</td>\n",
       "      <td>08:12:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>deer park cr and st clair</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4040</td>\n",
       "      <td>2016-01-02 08:12:00</td>\n",
       "      <td>[43.6872125, -79.3980108]</td>\n",
       "      <td>43.687213</td>\n",
       "      <td>-79.398011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>506</td>\n",
       "      <td>08:45:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>coxwell and gerrard</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4055</td>\n",
       "      <td>2016-01-02 08:45:00</td>\n",
       "      <td>[43.6753569, -79.32028249999999]</td>\n",
       "      <td>43.675357</td>\n",
       "      <td>-79.320282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>506</td>\n",
       "      <td>12:26:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>coxwell and gerrard</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4125</td>\n",
       "      <td>2016-01-02 12:26:00</td>\n",
       "      <td>[43.6753569, -79.32028249999999]</td>\n",
       "      <td>43.675357</td>\n",
       "      <td>-79.320282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>504</td>\n",
       "      <td>13:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>john-spadina</td>\n",
       "      <td>General Delay</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>bad vehicle</td>\n",
       "      <td>2016-01-02 13:00:00</td>\n",
       "      <td>[43.659681, -79.40069810000001]</td>\n",
       "      <td>43.659681</td>\n",
       "      <td>-79.400698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>506</td>\n",
       "      <td>19:24:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>ashdale and gerrard</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>23.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4042</td>\n",
       "      <td>2016-01-02 19:24:00</td>\n",
       "      <td>[43.6723671, -79.32211559999999]</td>\n",
       "      <td>43.672367</td>\n",
       "      <td>-79.322116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>510</td>\n",
       "      <td>19:39:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>spadina north of college</td>\n",
       "      <td>Investigation</td>\n",
       "      <td>66.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>n</td>\n",
       "      <td>4124</td>\n",
       "      <td>2016-01-02 19:39:00</td>\n",
       "      <td>[43.6579365, -79.3999151]</td>\n",
       "      <td>43.657936</td>\n",
       "      <td>-79.399915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>501</td>\n",
       "      <td>19:50:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>abell and queen</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>7.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4237</td>\n",
       "      <td>2016-01-02 19:50:00</td>\n",
       "      <td>[43.6430758, -79.4243556]</td>\n",
       "      <td>43.643076</td>\n",
       "      <td>-79.424356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>504</td>\n",
       "      <td>22:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>jameson and king</td>\n",
       "      <td>Investigation</td>\n",
       "      <td>26.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4187</td>\n",
       "      <td>2016-01-02 22:00:00</td>\n",
       "      <td>[43.6372723, -79.4358608]</td>\n",
       "      <td>43.637272</td>\n",
       "      <td>-79.435861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>501</td>\n",
       "      <td>22:50:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>queen street and roncesvalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4207</td>\n",
       "      <td>2016-01-02 22:50:00</td>\n",
       "      <td>[43.6390997, -79.4451902]</td>\n",
       "      <td>43.639100</td>\n",
       "      <td>-79.445190</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Report Date Route      Time       Day  \\\n",
       "0   2016-01-01   505  00:00:00    Friday   \n",
       "1   2016-01-01   511  02:14:00    Friday   \n",
       "2   2016-01-01   301  02:22:00    Friday   \n",
       "3   2016-01-01   301  03:28:00    Friday   \n",
       "4   2016-01-01   501  14:28:00    Friday   \n",
       "5   2016-01-01   505  15:42:00    Friday   \n",
       "6   2016-01-01   504  15:54:00    Friday   \n",
       "7   2016-01-01   501  16:05:00    Friday   \n",
       "8   2016-01-01   506  16:27:00    Friday   \n",
       "9   2016-01-01   510  16:34:00    Friday   \n",
       "10  2016-01-01   501  16:44:00    Friday   \n",
       "11  2016-01-01   505  17:36:00    Friday   \n",
       "12  2016-01-01   506  19:15:00    Friday   \n",
       "13  2016-01-01   509  21:25:00    Friday   \n",
       "14  2016-01-01   511  22:54:00    Friday   \n",
       "15  2016-01-01   510  23:58:00    Friday   \n",
       "16  2016-01-02   501  01:00:00  Saturday   \n",
       "17  2016-01-02   501  06:01:00  Saturday   \n",
       "18  2016-01-02   504  06:51:00  Saturday   \n",
       "19  2016-01-02   504  06:55:00  Saturday   \n",
       "20  2016-01-02   501  08:04:00  Saturday   \n",
       "21  2016-01-02   512  08:12:00  Saturday   \n",
       "22  2016-01-02   506  08:45:00  Saturday   \n",
       "23  2016-01-02   506  12:26:00  Saturday   \n",
       "24  2016-01-02   504  13:00:00  Saturday   \n",
       "25  2016-01-02   506  19:24:00  Saturday   \n",
       "26  2016-01-02   510  19:39:00  Saturday   \n",
       "27  2016-01-02   501  19:50:00  Saturday   \n",
       "28  2016-01-02   504  22:00:00  Saturday   \n",
       "29  2016-01-02   501  22:50:00  Saturday   \n",
       "\n",
       "                                     Location            Incident  Min Delay  \\\n",
       "0   dundas west stationt to broadview station       General Delay        7.0   \n",
       "1                      fleet st. and strachan          Mechanical       10.0   \n",
       "2             queen st. west and roncesvalles          Mechanical        9.0   \n",
       "3           lake shore blvd. and superior st.          Mechanical       20.0   \n",
       "4                roncesvalles to neville park          Mechanical        6.0   \n",
       "5                      broadview station loop       Investigation        4.0   \n",
       "6                         broadview and queen          Mechanical        6.0   \n",
       "7                 roncesvalles to humber loop          Mechanical        6.0   \n",
       "8                                main station          Mechanical        8.0   \n",
       "9                    richmond st. and spadina           Diversion       41.0   \n",
       "10                     roncesvalles to humber          Mechanical        6.0   \n",
       "11              dundas st. west and huron st.             Held By        7.0   \n",
       "12                       bathurst and college  Utilized Off Route        7.0   \n",
       "13                                   cne loop          Mechanical       10.0   \n",
       "14                    bathurst and wellington             Held By       16.0   \n",
       "15                queen quay loop and spadina          Mechanical        6.0   \n",
       "16           roncesvallesue and the queensway          Mechanical       10.0   \n",
       "17                      queen and roncevalles          Mechanical        4.0   \n",
       "18                           king and spadina          Mechanical       32.0   \n",
       "19                           king and spadina          Mechanical       28.0   \n",
       "20                                humber loop          Mechanical        8.0   \n",
       "21                  deer park cr and st clair          Mechanical        9.0   \n",
       "22                        coxwell and gerrard          Mechanical        8.0   \n",
       "23                        coxwell and gerrard          Mechanical        6.0   \n",
       "24                               john-spadina       General Delay       10.0   \n",
       "25                        ashdale and gerrard          Mechanical       23.0   \n",
       "26                   spadina north of college       Investigation       66.0   \n",
       "27                            abell and queen          Mechanical        7.0   \n",
       "28                           jameson and king       Investigation       26.0   \n",
       "29              queen street and roncesvalles          Mechanical        9.0   \n",
       "\n",
       "    Min Gap Direction      Vehicle    Report Date Time  \\\n",
       "0      14.0         w         4028 2016-01-01 00:00:00   \n",
       "1      20.0         e         4018 2016-01-01 02:14:00   \n",
       "2      18.0         w         4201 2016-01-01 02:22:00   \n",
       "3      40.0         e         4251 2016-01-01 03:28:00   \n",
       "4      12.0         e         4242 2016-01-01 14:28:00   \n",
       "5      10.0         w         4187 2016-01-01 15:42:00   \n",
       "6      12.0         e         4181 2016-01-01 15:54:00   \n",
       "7      12.0         w         4245 2016-01-01 16:05:00   \n",
       "8      16.0         w         4092 2016-01-01 16:27:00   \n",
       "9      46.0         s  bad vehicle 2016-01-01 16:34:00   \n",
       "10     12.0         w         4220 2016-01-01 16:44:00   \n",
       "11     13.0         w         4039 2016-01-01 17:36:00   \n",
       "12     14.0         w         4095 2016-01-01 19:15:00   \n",
       "13     20.0         e         4406 2016-01-01 21:25:00   \n",
       "14     24.0         b         4095 2016-01-01 22:54:00   \n",
       "15     13.0         n         4132 2016-01-01 23:58:00   \n",
       "16     20.0         e         4223 2016-01-02 01:00:00   \n",
       "17     30.0         e         4247 2016-01-02 06:01:00   \n",
       "18     36.0         w         4186 2016-01-02 06:51:00   \n",
       "19     32.0         w         4154 2016-01-02 06:55:00   \n",
       "20     15.0         e         4208 2016-01-02 08:04:00   \n",
       "21     18.0         w         4040 2016-01-02 08:12:00   \n",
       "22     15.0         w         4055 2016-01-02 08:45:00   \n",
       "23     11.0         e         4125 2016-01-02 12:26:00   \n",
       "24     14.0         w  bad vehicle 2016-01-02 13:00:00   \n",
       "25     29.0         e         4042 2016-01-02 19:24:00   \n",
       "26     12.0         n         4124 2016-01-02 19:39:00   \n",
       "27     15.0         e         4237 2016-01-02 19:50:00   \n",
       "28     34.0         w         4187 2016-01-02 22:00:00   \n",
       "29     18.0         w         4207 2016-01-02 22:50:00   \n",
       "\n",
       "                            lat_long   latitude  longitude  \n",
       "0                         [0.0, 0.0]   0.000000   0.000000  \n",
       "1          [43.6362976, -79.4096351]  43.636298 -79.409635  \n",
       "2   [43.64533489999999, -79.4131843]  43.645335 -79.413184  \n",
       "3   [43.61496169999999, -79.4886581]  43.614962 -79.488658  \n",
       "4                         [0.0, 0.0]   0.000000   0.000000  \n",
       "5    [43.677135, -79.35820799999999]  43.677135 -79.358208  \n",
       "6   [43.6593626, -79.34769709999999]  43.659363 -79.347697  \n",
       "7                         [0.0, 0.0]   0.000000   0.000000  \n",
       "8          [43.6890219, -79.3016857]  43.689022 -79.301686  \n",
       "9   [43.6478469, -79.39588049999999]  43.647847 -79.395880  \n",
       "10                        [0.0, 0.0]   0.000000   0.000000  \n",
       "11          [43.653332, -79.3961386]  43.653332 -79.396139  \n",
       "12         [43.6564647, -79.4077012]  43.656465 -79.407701  \n",
       "13  [43.63441590000001, -79.4258807]  43.634416 -79.425881  \n",
       "14         [43.6426297, -79.4021618]  43.642630 -79.402162  \n",
       "15         [43.6384598, -79.3916564]  43.638460 -79.391656  \n",
       "16   [43.63878890000001, -79.446111]  43.638789 -79.446111  \n",
       "17  [43.64533489999999, -79.4131843]  43.645335 -79.413184  \n",
       "18         [43.6454419, -79.3951458]  43.645442 -79.395146  \n",
       "19         [43.6454419, -79.3951458]  43.645442 -79.395146  \n",
       "20   [43.63079099999999, -79.478622]  43.630791 -79.478622  \n",
       "21         [43.6872125, -79.3980108]  43.687213 -79.398011  \n",
       "22  [43.6753569, -79.32028249999999]  43.675357 -79.320282  \n",
       "23  [43.6753569, -79.32028249999999]  43.675357 -79.320282  \n",
       "24   [43.659681, -79.40069810000001]  43.659681 -79.400698  \n",
       "25  [43.6723671, -79.32211559999999]  43.672367 -79.322116  \n",
       "26         [43.6579365, -79.3999151]  43.657936 -79.399915  \n",
       "27         [43.6430758, -79.4243556]  43.643076 -79.424356  \n",
       "28         [43.6372723, -79.4358608]  43.637272 -79.435861  \n",
       "29         [43.6390997, -79.4451902]  43.639100 -79.445190  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(69603, 14)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bad route latitude: 1675\n"
     ]
    }
   ],
   "source": [
    "print(\"Bad route latitude:\",df_out[df_out.latitude == 0.0].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove bad rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Location count post cleanup: 10074\n",
      "Route count post cleanup: 15\n",
      "Direction count post cleanup: 6\n",
      "Vehicle count post cleanup: 1017\n",
      "Bad route count: 3091\n",
      "Bad direction count: 334\n",
      "Bad vehicle count: 14480\n"
     ]
    }
   ],
   "source": [
    "print(\"Location count post cleanup:\",df['Location'].nunique())\n",
    "print(\"Route count post cleanup:\",df['Route'].nunique())\n",
    "print(\"Direction count post cleanup:\",df['Direction'].nunique())\n",
    "print(\"Vehicle count post cleanup:\",df['Vehicle'].nunique())\n",
    "# print(\"Bad Location count\":df[df.Vehicle == 'bad vehicle'].shape[0])\n",
    "print(\"Bad route count:\",df[df.Route == 'bad route'].shape[0])\n",
    "print(\"Bad direction count:\",df[df.Direction == 'bad direction'].shape[0])\n",
    "print(\"Bad vehicle count:\",df[df.Vehicle == 'bad vehicle'].shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove rows with bad vehicle value\n",
    "if remove_bad_values:\n",
    "    df = df[df.Vehicle != 'bad vehicle']\n",
    "    df = df[df.Direction != 'bad direction']\n",
    "    df = df[df.Route != 'bad route']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66095, 11)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2014_2018_df_cleaned_keep_bad_loc_geocoded_apr23.pkl'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pickled_output_dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pickle the cleansed dataframe\n",
    "file_name = path + pickled_output_dataframe\n",
    "df_out.to_pickle(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Report Date</th>\n",
       "      <th>Route</th>\n",
       "      <th>Time</th>\n",
       "      <th>Day</th>\n",
       "      <th>Location</th>\n",
       "      <th>Incident</th>\n",
       "      <th>Min Delay</th>\n",
       "      <th>Min Gap</th>\n",
       "      <th>Direction</th>\n",
       "      <th>Vehicle</th>\n",
       "      <th>Report Date Time</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>505</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>dundas west stationt to broadview station</td>\n",
       "      <td>General Delay</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4028</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>511</td>\n",
       "      <td>02:14:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>fleet st. and strachan</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4018</td>\n",
       "      <td>2016-01-01 02:14:00</td>\n",
       "      <td>[43.6362976, -79.4096351]</td>\n",
       "      <td>43.636298</td>\n",
       "      <td>-79.409635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>02:22:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>queen st. west and roncesvalles</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>w</td>\n",
       "      <td>4201</td>\n",
       "      <td>2016-01-01 02:22:00</td>\n",
       "      <td>[43.64533489999999, -79.4131843]</td>\n",
       "      <td>43.645335</td>\n",
       "      <td>-79.413184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>301</td>\n",
       "      <td>03:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>lake shore blvd. and superior st.</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4251</td>\n",
       "      <td>2016-01-01 03:28:00</td>\n",
       "      <td>[43.61496169999999, -79.4886581]</td>\n",
       "      <td>43.614962</td>\n",
       "      <td>-79.488658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>501</td>\n",
       "      <td>14:28:00</td>\n",
       "      <td>Friday</td>\n",
       "      <td>roncesvalles to neville park</td>\n",
       "      <td>Mechanical</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>e</td>\n",
       "      <td>4242</td>\n",
       "      <td>2016-01-01 14:28:00</td>\n",
       "      <td>[0.0, 0.0]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Report Date Route      Time     Day  \\\n",
       "0  2016-01-01   505  00:00:00  Friday   \n",
       "1  2016-01-01   511  02:14:00  Friday   \n",
       "2  2016-01-01   301  02:22:00  Friday   \n",
       "3  2016-01-01   301  03:28:00  Friday   \n",
       "4  2016-01-01   501  14:28:00  Friday   \n",
       "\n",
       "                                    Location       Incident  Min Delay  \\\n",
       "0  dundas west stationt to broadview station  General Delay        7.0   \n",
       "1                     fleet st. and strachan     Mechanical       10.0   \n",
       "2            queen st. west and roncesvalles     Mechanical        9.0   \n",
       "3          lake shore blvd. and superior st.     Mechanical       20.0   \n",
       "4               roncesvalles to neville park     Mechanical        6.0   \n",
       "\n",
       "   Min Gap Direction Vehicle    Report Date Time  \\\n",
       "0     14.0         w    4028 2016-01-01 00:00:00   \n",
       "1     20.0         e    4018 2016-01-01 02:14:00   \n",
       "2     18.0         w    4201 2016-01-01 02:22:00   \n",
       "3     40.0         e    4251 2016-01-01 03:28:00   \n",
       "4     12.0         e    4242 2016-01-01 14:28:00   \n",
       "\n",
       "                           lat_long   latitude  longitude  \n",
       "0                        [0.0, 0.0]   0.000000   0.000000  \n",
       "1         [43.6362976, -79.4096351]  43.636298 -79.409635  \n",
       "2  [43.64533489999999, -79.4131843]  43.645335 -79.413184  \n",
       "3  [43.61496169999999, -79.4886581]  43.614962 -79.488658  \n",
       "4                        [0.0, 0.0]   0.000000   0.000000  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfn = pd.read_pickle(file_name)\n",
    "dfn.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(69603, 14)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfn.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_outname = \"2014_2018_df_cleaned_keep_bad_loc_geocoded_apr29.csv\"\n",
    "dfn.to_csv(path+file_outname)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Visualize cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install pixiedust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pixiedust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pixiedust": {
     "displayParams": {
      "binsize": "23",
      "chartsize": "70",
      "handlerId": "barChart",
      "keyFields": "Route",
      "mapboxtoken": "pk.eyJ1Ijoicnlhbm1hcmsiLCJhIjoiY2pvM2JnZmE0MHY5dzNxcXM0Zmw3c2RwZSJ9.k-PhU8qMVztXtdyuX2Yjpw",
      "sortby": "Values DESC",
      "stretch": "false",
      "tableFields": "Day",
      "valueFields": "Min Delay"
     }
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "display(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
